﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Reflection;
using DAL.EstRec;

namespace BAL.EstRec
{

    public class ExportExcel
    {
        public string VersionID { get; set; }
        public string Path { get; set; }
        public bool HasHeader { get; set; }
        public string currentPositionCode { get; set; }
        private string[] Columns = { "POS", "RECURSOS", "CANTIDAD" };

        private XLSData xlsArgs;

        public ExportExcel(string Path, bool HasHeader, string WhereClause)
        {
            this.Path = Path;
            this.HasHeader = HasHeader;
            this.VersionID = WhereClause;

        }

        public ExportExcel(string Path, bool HasHeader, string WhereClause, string currentPositionCode)
        {
            this.Path = Path;
            this.HasHeader = HasHeader;
            this.VersionID = WhereClause;
            this.currentPositionCode = currentPositionCode;
            this.xlsArgs = new XLSData();
        }

        public ExportExcel() { }

        public void exportHQ()
        {
            string querry;

            querry = "";

            querry += "select p.ID_POS, r.ID_RECURSO,hq.CANTIDAD";
            querry += " from dbo.HQBGT hq";
            querry += " inner join  HQBGT_VERSION hqv";
            querry += " on hq.UNID_HQBGT_VERSION = hqv.UNID_HQBGT_VERSION";
            querry += " inner join dbo.POS p";
            querry += " ON hq.ID_POS = p.ID_POS";
            querry += " inner join dbo.RECURSO r";
            querry += " ON hq.ID_RECURSO = r.ID_RECURSO";
            querry += " where hq.UNID_HQBGT_VERSION=";

            querry += VersionID;

            System.Data.DataTable table = SrvDB.ExecuteQuery(querry);

            DataToExcel(table, false, Path);
        }

        public void exportClipBoard()
        {
            ClipboardToExcel(false, Path);
        }


        public void exportFDV()
        {
            string querry;

            querry = "";

            querry += " select p.ID_POS, r.ID_RECURSO,hq.CANTIDAD from dbo.FDV_BGT hq ";
            querry += " inner join FDV_BGT_VERSION hqv ";
            querry += " on hq.UNID_FDV_BGT_VERSION = hqv.UNID_FDV_BGT_VERSION ";
            querry += " inner join dbo.POS p ";
            querry += " ON hq.ID_POS = p.ID_POS ";
            querry += " inner join dbo.RECURSO r ";
            querry += " ON hq.ID_RECURSO = r.ID_RECURSO ";
            querry += " inner join dbo.R_TERRITORIO_POS rrpp ";
            querry += " ON rrpp.ID_POS = p.ID_POS ";
            querry += " inner join dbo.TERRITORIO terr";
            querry += " ON terr.ID_TERRITORIO = rrpp.ID_TERRITORIO ";

            querry += " where hq.UNID_FDV_BGT_VERSION=" + VersionID;
            querry += " AND terr.ID_TERRITORIO = '"+currentPositionCode+"'";

            System.Data.DataTable table = SrvDB.ExecuteQuery(querry);

            DataToExcel(table, false, Path);
        }


        public void export()
        {
            string querry;

            querry = "";
            
            querry += "select p.ID_POS, r.ID_RECURSO,hq.CANTIDAD";
	        querry += " from dbo.HQBGT hq";	
	        querry += " inner join  HQBGT_VERSION hqv";
		    querry += " on hq.UNID_HQBGT_VERSION = hqv.UNID_HQBGT_VERSION";
	        querry += " inner join dbo.POS p";
		    querry += " ON hq.ID_POS = p.ID_POS";
	        querry += " inner join dbo.RECURSO r";
	        querry += " ON hq.ID_RECURSO = r.ID_RECURSO";
	        querry += " where hq.UNID_HQBGT_VERSION=";
            
            querry += VersionID;

            System.Data.DataTable table = SrvDB.ExecuteQuery(querry);

            DataToExcel(table, false, Path);
        }

        public void DataToExcel2(System.Data.DataTable dt, bool isShowExcel, string filepath)
        {
            try
            {
                if (dt.Rows.Count == 0)
                {
                    System.Windows.MessageBox.Show("No hay datos para exportar a Excel en esta versión, por lo que no se creará el archivo");
                    return;
                }
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Application.Workbooks.Add(true);
                excel.Visible = isShowExcel;

                Workbook excelPrint = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet excelSheetPrint = (Worksheet)excelPrint.Worksheets[1];

                int maxCol = dt.Columns.Count;
                int maxRow = dt.Rows.Count;

                //Title//Header

                if (HasHeader)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        excel.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
                    }
                }

                //Fill data
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        object dtType = dt.Rows[i][j].GetType();
                        if (dt.Rows[i][j].GetType() == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "'" + dt.Rows[i][j].ToString();
                        }
                        else
                        {
                            string temp = Convert.ToString(dt.Rows[i][j]);
                            excel.Cells[i + 2, j + 1] = dt.Rows[i][j];

                        }
                    }
                }
                
                
                string dtime = DateTime.Now.ToString().Replace('/', '_');
                dtime = dtime.Replace(':', '_');
                excelSheetPrint.SaveAs(filepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excel.Quit();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }





        public void DataToExcel(System.Data.DataTable dt, bool isShowExcel, string filepath)
        {
            try
            {
                if (dt.Rows.Count == 0)
                {
                    System.Windows.MessageBox.Show("No hay datos para exportar a Excel en esta versión, por lo que no se creará el archivo");
                    return;
                }

                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Application.Workbooks.Add(true);
                excel.Visible = isShowExcel;

                Workbook excelPrint = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet excelSheetPrint = (Worksheet)excelPrint.Worksheets[1];

                int maxCol = dt.Columns.Count;
                int maxRow = dt.Rows.Count;

                //Title//Header

                if (HasHeader)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        excel.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
                    }
                }

                //Fill data
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        object dtType = dt.Rows[i][j].GetType();
                        if (dt.Rows[i][j].GetType() == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "'" + dt.Rows[i][j].ToString();
                        }
                        else
                        {
                            string temp = Convert.ToString(dt.Rows[i][j]);
                            excel.Cells[i + 2, j + 1] = dt.Rows[i][j];

                        }
                    }
                }

                string dtime = DateTime.Now.ToString().Replace('/', '_');
                dtime = dtime.Replace(':', '_');
                excelSheetPrint.SaveAs(Path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excel.Quit();


            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ClipboardToExcel(bool isShowExcel, string filepath)
        {
            ClipboardToExcel(isShowExcel, filepath, true);
        }

        public void ClipboardToExcel(bool isShowExcel, string filepath,bool deleteFirstColumn)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Application.Workbooks.Add(true);
                excel.Visible = isShowExcel;

                Workbook excelPrint = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet excelSheetPrint = (Worksheet)excelPrint.Worksheets[1];

                excelSheetPrint.Activate();
                excelSheetPrint.Paste();

                if (deleteFirstColumn)
                {
                    ((Microsoft.Office.Interop.Excel.Range)(excelSheetPrint.Cells[1, 1])).EntireColumn.Delete(null);
                }
                    
                excelSheetPrint.SaveAs(Path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excel.Quit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }


}
